package com.pangu.export.utils;

import com.pangu.export.annotation.ExcelCell;
import com.pangu.export.constant.ExcelType;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.AccessibleObject;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Created by etfox on 2019/10/26 15:23
 */
public class ExcelReadUtil {

    /**
     * 读取excel
     *
     * @param file       文件
     * @param path       路径
     * @param fileName   文件名
     * @param fileType   文件类型
     * @param startSheet startSheet
     * @param startRow   起始行
     * @param clazz      反射Clazz类型
     * @return t
     */
    public static <T> List<T> readExcel(File file, String path, String fileName, String fileType, Integer startSheet, Integer startRow, Class<T> clazz) {
        List<T> entityList = new ArrayList<>();
        //HSSFWorkbook、XSSFWorkbook
        Workbook workbook;
        try (FileInputStream fileInputStream = creatWorKStream(file, path, fileName, fileType)) {
            workbook = createWorkbook(fileInputStream, fileType);
            //起始工作簿
            if (null == startSheet) {
                startSheet = 0;
            }
            if (null == startRow) {
                startRow = 1;
            }

            //字段对应列
            Map<String, Integer> cellMap = new HashMap<>();
            for (Field _f : clazz.getDeclaredFields()) {
                ExcelCell _cell = _f.getAnnotation(ExcelCell.class);
                if (_cell != null) {
                    cellMap.put(_f.getName(), _cell.cellY());
                }
            }

            //HSSFSheet、XSSFSheet
            Sheet sheet = workbook.getSheetAt(startSheet);
            if (null != sheet) {
                for (int rowNum = startRow; rowNum <= sheet.getLastRowNum(); rowNum++) {
                    //HSSFRow、XSSFRow
                    Row row = sheet.getRow(rowNum);
                    if (null == row) {
                        continue;
                    }
                    T entity = clazz.newInstance();
                    for (Map.Entry<String, Integer> cellEntry : cellMap.entrySet()) {
                        String fieldName = cellEntry.getKey();
                        int cellPos = cellEntry.getValue();
                        //获取对象中字段的值
                        Field field = clazz.getDeclaredField(fieldName);
                        if (null == field) {
                            continue;
                        }
                        ExcelCell exCell = field.getAnnotation(ExcelCell.class);
                        String exformat = exCell.format();
                        field.setAccessible(true);
                        String value = formatCell(row.getCell(cellPos), exformat);
                        typeMapper(field, entity, value, exformat);
                    }
                    //添加
                    entityList.add(entity);
                }
            }
        } catch (Throwable e) {
            e.printStackTrace();
        }
        return entityList;
    }

    /**
     * @param file
     * @param path
     * @param fileName
     * @param fileType
     * @param clazz
     * @return t
     */
    public static <T> T readExcel(File file, String path, String fileName, String fileType, Class<T> clazz) {
        try {
            T object = clazz.newInstance();
            return readExcel(file, path, fileName, fileType, object);
        } catch (InstantiationException | IllegalAccessException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 读取复杂excel
     *
     * @param file
     * @param path
     * @param fileName
     * @param fileType
     * @param object
     * @return t
     */
    public static <T> T readExcel(File file, String path, String fileName, String fileType, T object) {
        if (null == object) {
            return object;
        }
        //HSSFWorkbook、XSSFWorkbook
        Workbook workbook;
        try (FileInputStream fileInputStream = creatWorKStream(file, path, fileName, fileType)) {
            workbook = createWorkbook(fileInputStream, fileType);
            //遍历Field
            Field[] objectFields = new Field[0];
            for (Class<?> superClass = object.getClass(); superClass != Object.class; superClass = superClass.getSuperclass()) {
                try {
                    objectFields = ArrayUtils.addAll(objectFields, superClass.getDeclaredFields());
                } catch (SecurityException ignored) {
                }
            }
            //设置访问权限
            AccessibleObject.setAccessible(objectFields, true);
            for (Field field : objectFields) {
                //获取Annotation sheet/cellX/cellY
                ExcelCell excelCell = field.getAnnotation(ExcelCell.class);
                if (null == excelCell) {
                    continue;
                }
                int sheetNum = excelCell.sheet();
                int cellXNum = excelCell.cellX();
                int cellYNum = excelCell.cellY();
                String excelFormat = excelCell.format();
                Sheet sheet = workbook.getSheetAt(sheetNum);
                //获取合并单元格
                Cell cell = getMergedRegionCell(sheet, cellXNum, cellYNum);
                if (null == cell) {
                    Row row = sheet.getRow(cellXNum);
                    cell = row.getCell(cellYNum);
                }
                String value = formatCell(cell, excelFormat);
                typeMapper(field, object, value, excelFormat);
            }
        } catch (Throwable e) {
            //e.printStackTrace();
            //Log.getLog().debug("读取excel失败"+e.getMessage());
        }
        return object;
    }

    /**
     * 创建Workbook (从原readExcel方法中抽取出来公用)
     *
     * @param file     file
     * @param path     path
     * @param fileName fileName
     * @param fileType fileType
     * @return workbook
     * @throws Exception e
     */
    public static Workbook createWorkbook(File file, String path, String fileName, String fileType) throws Exception {
        //HSSFWorkbook、XSSFWorkbook
        FileInputStream fileInputStream = creatWorKStream(file, path, fileName, fileType);

        return createWorkbook(fileInputStream, fileType);
    }

    /**
     * 创建Workbook(从原readExcel方法中抽取出来公用)
     *
     * @param fileInputStream fileInputStream
     * @param fileType        fileType
     * @return workbook
     * @throws Exception e
     */
    public static Workbook createWorkbook(FileInputStream fileInputStream, String fileType) throws Exception {
        POIFSFileSystem poifsFileSystem = null;
        Workbook workbook = null;
        if (null == fileInputStream) {
            return null;
        }
        //格式判断  此处fileType 已经被赋值为 creatWorKStream方法中的newfileType
        if (ExcelType.EXCELTYPE_03.equals(fileType)) {
            poifsFileSystem = new POIFSFileSystem(fileInputStream);
            workbook = new HSSFWorkbook(poifsFileSystem);
        } else if (ExcelType.EXCELTYPE_07.equals(fileType)) {
            workbook = new XSSFWorkbook(fileInputStream);
        }
        return workbook;
    }

    /**
     * 获取WorKStream 文件流(单独获取,用于后续关闭文件流)
     *
     * @param file     file
     * @param path     path
     * @param fileName fileName
     * @param fileType fileType
     * @return fileInputStream
     * @throws Exception e
     */
    public static FileInputStream creatWorKStream(File file, String path, String fileName, String fileType) throws Exception {
        File newFile;
        String newFileType;
        FileInputStream fileInputStream = null;
        // 创建文件
        if (null != file) {
            newFile = file;
        } else {
            // 判断格式
            if (null == fileName || "".equals(fileName)) {
                return null;
            }
            if (null != fileType && !"".equals(fileType)) {
                newFileType = fileType;
            } else if (0 < fileName.lastIndexOf(".")) {
                newFileType = fileName.substring(fileName.lastIndexOf("."));
            } else {
                return null;
            }

            if (ExcelType.EXCELTYPE_03.equals(newFileType) || ExcelType.EXCELTYPE_07.equals(newFileType)) {
                if (0 < fileName.lastIndexOf(".") || 0 < path.lastIndexOf(".")) {
                    newFile = new File(path);
                } else {
                    newFile = new File(path, fileName + newFileType);
                }
            } else {
                return null;
            }
        }
        fileInputStream = new FileInputStream(newFile);

        return fileInputStream;
    }

    /**
     * 判断是否是合并单元格
     *
     * @param sheet   sheet
     * @param cellRow cellRow
     * @param cellCol cellCol
     * @return boolean
     */
    public static boolean isMergedRegion(Sheet sheet, int cellRow, int cellCol) {
        int sheetMergerCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress cra = sheet.getMergedRegion(i);
            int firstRow = cra.getFirstRow(); // 合并单元格cell起始行
            int firstCol = cra.getFirstColumn(); // 合并单元格cell起始列
            int lastRow = cra.getLastRow(); // 合并单元格cell结束行
            int lastCol = cra.getLastColumn(); // 合并单元格cell结束列
            if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
                if (cellCol >= firstCol && cellCol <= lastCol) {
                    return true; // 返回true
                }
            }
        }
        return false;
    }

    /**
     * 获取合并单元格
     *
     * @param sheet   sheet
     * @param cellRow cellRow
     * @param cellCol cellCol
     * @return cell
     */
    public static Cell getMergedRegionCell(Sheet sheet, int cellRow, int cellCol) {
        int sheetMergerCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress cra = sheet.getMergedRegion(i);
            int firstRow = cra.getFirstRow(); // 合并单元格cell起始行
            int firstCol = cra.getFirstColumn(); // 合并单元格cell起始列
            int lastRow = cra.getLastRow(); // 合并单元格cell结束行
            int lastCol = cra.getLastColumn(); // 合并单元格cell结束列
            if (cellRow >= firstRow && cellRow <= lastRow) { // 判断该单元格是否是在合并单元格中
                if (cellCol >= firstCol && cellCol <= lastCol) {
                    // 得到合并的行数 lastRow - firstRow + 1;
                    // 得到合并的列数 lastCol - firstCol + 1;
                    Row row = sheet.getRow(firstRow);
                    return row.getCell(firstCol);
                }
            }
        }
        return null;
    }

    /**
     * 类型转换
     *
     * @param field       字段
     * @param object      对象
     * @param value       值
     * @param excelFormat 格式
     * @throws Exception e
     */
    public static void typeMapper(Field field, Object object, String value, String excelFormat) throws Exception {
        Class<?> type = field.getType();
        if (type == String.class) {
            field.set(object, value);
        } else if (type == Integer.class || type == int.class) {
            field.set(object, Integer.parseInt(value));
        } else if (type == Long.class || type == long.class) {
            field.set(object, Long.valueOf(value));
        } else if (type == Boolean.class || type == boolean.class) {
            field.set(object, Boolean.valueOf(value));
        } else if (type == Date.class) {
            String strDate = value;
            //field.set(entity, StringX.parseDate(strDate));
            field.set(object, (null == strDate || strDate.trim().length() == 0) ? null : new SimpleDateFormat(excelFormat).parse(strDate));
        } else if (type == BigDecimal.class) {
            field.set(object, Double.parseDouble(value));
        } else if (type == Double.class || type == double.class) {
            field.set(object, Double.parseDouble(value));
        }
    }

    /**
     * 格式化单元格值
     *
     * @param cell   cell
     * @param format format
     * @return cellValue
     */
    public static String formatCell(Cell cell, String format) {
        String cellValue = "";
        try {
//            if (null == cell || Cell.CELL_TYPE_BLANK == cell.getCellType()) {
//                cellValue = "";
//            } else
            if (Cell.CELL_TYPE_BOOLEAN == cell.getCellType()) {
                cellValue = String.valueOf(cell.getBooleanCellValue());
            } else if (Cell.CELL_TYPE_NUMERIC == cell.getCellType()) {
                if (DateUtil.isCellDateFormatted(cell)) {
                    if (null == format || "".equals(format)) {
                        cellValue = cell.getDateCellValue().toString();
                    } else {
                        cellValue = cell.getDateCellValue() == null ? "" : new SimpleDateFormat(format).format(cell.getDateCellValue());
                    }
                } else {
                    if (null == format || "".equals(format)) {
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    } else {
                        DecimalFormat df = new DecimalFormat(format);
                        cellValue = df.format(cell.getNumericCellValue());
                    }

                }
            } else if (Cell.CELL_TYPE_FORMULA == cell.getCellType()) {
                FormulaEvaluator evaluator = cell.getSheet().getWorkbook()
                        .getCreationHelper().createFormulaEvaluator();
                evaluator.evaluateFormulaCell(cell);
                cellValue = String
                        .valueOf(evaluator.evaluate(cell).getNumberValue());
            } else {
                cellValue = String.valueOf(cell.getStringCellValue());
            }
        } catch (Exception e) {
            // .ignore
        }
        return cellValue;
    }
}
